from pymysql import Connection

from utils.file_define import TextFileReader, JsonFileReader

conn = None
try:
    conn = Connection(host='localhost', user='root', password='31000025', database='python')
    cursor = conn.cursor()
    cursor.execute("""CREATE TABLE IF NOT EXISTS sale (
        id INT AUTO_INCREMENT PRIMARY KEY,
        date DATE NOT NULL,
        order_id VARCHAR(50) NOT NULL UNIQUE,
        money DECIMAL(10, 2) NOT NULL,
        province VARCHAR(50) NOT NULL,
        INDEX idx_date (date),
        INDEX idx_order_id (order_id),
        INDEX idx_province (province)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;""")

    text_file_reader = TextFileReader("2011年1月销售数据.txt")
    json_file_reader = JsonFileReader("2011年2月销售数据JSON.txt")
    jan_data = text_file_reader.read_data()
    feb_data = json_file_reader.read_data()
    all_data = jan_data + feb_data

    # data_to_insert = [
    #     (record.date, record.order_id, record.money, record.province)
    #     for record in all_data
    # ]
    # print(data_to_insert)

    all_data_sql = []
    for record in all_data:
        all_data_sql.append((record.date, record.order_id, record.money, record.province))
    sql = f"INSERT INTO sale (date, order_id, money, province) VALUES (%s,%s,%s,%s);"
    cursor.executemany(sql, all_data_sql)
    conn.commit()

except Exception as e:
    print(e)
    if conn:
        conn.rollback()

finally:
    print("finally", end='')
    if conn:
        print("关闭数据库连接")
        conn.close()
